package live.zs.excelurltoimg.service.impl;

import live.zs.excelurltoimg.service.ExcelHyperlinkToImageService;
import org.apache.poi.ss.usermodel.*;
import org.apache.poi.ss.util.ImageUtils;
import org.apache.poi.util.IOUtils;
import org.apache.poi.util.Units;
import org.apache.poi.xssf.usermodel.XSSFWorkbook;
import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;

import java.io.File;
import java.io.FileInputStream;
import java.io.FileOutputStream;
import java.io.InputStream;
import java.util.ArrayList;
import java.util.List;

/**
 * @author madison
 * {@code @description}
 * {@code @date} 2023/12/16 17:17
 */
@Service
public class ExcelHyperlinkToImageServiceImpl implements ExcelHyperlinkToImageService {

    private static final Logger logger = LoggerFactory.getLogger(ExcelHyperlinkToImageServiceImpl.class);

    @Override
    public void convert(String excelFilePath, String toExcelFilePath, String unzipDest) throws Exception {
        FileInputStream file = new FileInputStream(excelFilePath);
        Workbook workbook = new XSSFWorkbook(file);
        Sheet sheet = workbook.getSheetAt(0);

        // 遍历所有行和列
        for (Row row : sheet) {
            for (Cell cell : row) {
                // 检查单元格是否包含超链接
                Hyperlink hyperlink = cell.getHyperlink();
                if (hyperlink != null) {
                    String imagePath = hyperlink.getAddress();
                    logger.info("imagePath:{}", imagePath);
                    int i = imagePath.lastIndexOf('.');
                    String extension = "";
                    if (i > 0) {
                        extension = imagePath.substring(i + 1).toLowerCase();
                    }

                    List<String> filePath = new ArrayList<>();
                    File file1 = new File(unzipDest + imagePath);
                    if (file1.isDirectory()) {
                        File[] files = file1.listFiles(file2 -> file2.getName().endsWith(".jpg") || file2.getName().endsWith(".png")
                                || file2.getName().endsWith(".jpeg") || file2.getName().endsWith(".gif"));
                        for (File file2 : files) {
                            filePath.add(file2.getAbsolutePath());
                        }
                    } else {
                        filePath.add(unzipDest + imagePath);
                    }

                    for (String path : filePath) {
                        int index = 0;
                        // 读取本地图片
                        InputStream inputStream = new FileInputStream(path);
                        byte[] bytes = IOUtils.toByteArray(inputStream);
                        int pictureIdx;
                        if ("jpg".equalsIgnoreCase(extension) || "jpeg".equalsIgnoreCase(extension)) {
                            pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_JPEG);
                        } else if ("png".equalsIgnoreCase(extension)) {
                            pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PNG);
                        } else {
                            pictureIdx = workbook.addPicture(bytes, Workbook.PICTURE_TYPE_PICT);
                        }
                        inputStream.close();

                        // 创建帮助类用于插入图片
                        CreationHelper helper = workbook.getCreationHelper();
                        Drawing<?> drawing = sheet.createDrawingPatriarch();

                        // 设置图片位置
                        ClientAnchor anchor = helper.createClientAnchor();
                        anchor.setCol1(cell.getColumnIndex());
                        anchor.setRow1(row.getRowNum());
                        anchor.setCol2(cell.getColumnIndex() + 1); // 设置列宽
                        anchor.setRow2(row.getRowNum() + 1); // 设置行高
                        anchor.setDx1(index);
                        anchor.setDy1(index);
                        index += 10;
                        anchor.setDx2(index);
                        anchor.setDy2(index);

                        // 插入图片
                        Picture pict = drawing.createPicture(anchor, pictureIdx);
                        // 设置图片的宽度和高度
//                        ImageUtils.getDimensionFromAnchor(pict).setSize(10, 10); // 设置为10个字符的宽度
//                        int pictureHeightUnits = ImageUtils.getRowHeightUnits(20); // 设置为20个行高的高度
//                        int emuWidth = Units.pixelToEMU(10);
//                        int emuHeight = Units.pixelToEMU(10);
//                         pict.resize();// 自动调整大小
                    }

                    // 清除原有的超链接
                    cell.setHyperlink(null);
                    cell.setCellValue(""); // 清除原有文本
                    sheet.setColumnWidth(cell.getColumnIndex(), 2000);
                }
            }
        }

        logger.info("转换完成");
        // 写出到文件
        FileOutputStream out = new FileOutputStream(toExcelFilePath);
        workbook.write(out);
        out.close();
        workbook.close();
    }
}
